﻿using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using STT.Business;
using STT.Entities;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace STT.Admin.WebForms
{
    public partial class ucKhoaHoc : System.Web.UI.UserControl
    {
        public bool chonSuaClick;
        protected void Page_Load(object sender, EventArgs e)
        {
            LoadKhoaHoc();

            btSua.Visible = false;
            btThemMoi.Visible = true;
        }

        void LoadKhoaHoc()
        {
            gvKhoaHoc.AutoGenerateColumns = false;
            gvKhoaHoc.DataSource = BTbKhoaHoc.SelectAll();
            gvKhoaHoc.DataBind();
        }

        protected void gvKhoaHoc_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvKhoaHoc.EditIndex = e.NewEditIndex;
            gvKhoaHoc.DataBind();
        }

        protected void gvKhoaHoc_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int MaKhoaHoc = int.Parse(gvKhoaHoc.DataKeys[e.RowIndex].Value.ToString());

            BTbKhoaHoc.Delete(MaKhoaHoc);
            LoadKhoaHoc();
        }

        protected void gvKhoaHoc_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gvKhoaHoc.EditIndex = -1;
            gvKhoaHoc.DataBind();
        }

        protected void lbThemMoi_Click(object sender, EventArgs e)
        {
            BTbKhoaHoc.Insert(new ETbKhoaHoc
            {
                TenKhoaHoc = txtTenKhoaHoc.Text,
                NoiDung = txtNoiDung.Text,
                DiaDiemHoc = txtDiaDiem.Text,
                ThoiGianBatDau = DateTime.Parse(txtThoiGianBatDau.Text),
                ThoiGianKetThuc = DateTime.Parse(txtThoiGianKetThuc.Text)
            });

            LoadKhoaHoc();
        }

        protected void lbXuatDuLieu_Click(object sender, EventArgs e)
        {

        }

        protected void lbXoaTat_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < gvKhoaHoc.Rows.Count; i++)
            {
                CheckBox cbChon = (CheckBox)gvKhoaHoc.Rows[i].Cells[0].FindControl("cbChon");
                if (cbChon != null && cbChon.Checked)
                {
                    BTbKhoaHoc.Delete(int.Parse(gvKhoaHoc.DataKeys[i].Value.ToString()));
                }
            }

            LoadKhoaHoc();
        }

        // Phân trang
        protected void gvKhoaHoc_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            gvKhoaHoc.PageIndex = e.NewPageIndex;
            gvKhoaHoc.DataBind();
        }

        protected void gvKhoaHoc_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "ChonSua")
            {
                // Lay id
                int index = Convert.ToInt32(e.CommandArgument);

                // Thay đổi tiêu đề
                txtTieuDe.InnerText = "Sửa khóa học";
                
                txtTenKhoaHoc.Text = gvKhoaHoc.Rows[index].Cells[2].Text;

                txtNoiDung.Text = gvKhoaHoc.Rows[index].Cells[3].Text;
                txtDiaDiem.Text = gvKhoaHoc.Rows[index].Cells[4].Text;
                txtThoiGianBatDau.Text = DateTime.Parse(gvKhoaHoc.Rows[index].Cells[5].Text).ToString("yyyy-MM-dd");
                txtThoiGianKetThuc.Text = DateTime.Parse(gvKhoaHoc.Rows[index].Cells[6].Text).ToString("yyyy-MM-dd");

                btSua.Visible = true;
                btThemMoi.Visible = false;
                chonSuaClick = true;

            }
        }

        protected void btSua_Click(object sender, EventArgs e)
        {

        }

        protected void btSearch_Click(object sender, ImageClickEventArgs e)
        {

        }

        protected void btImport_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string FileName = FileUpload1.FileName;
                string path = string.Concat(Server.MapPath("~/Document/" + FileUpload1.FileName));

                FileUpload1.PostedFile.SaveAs(path);

                OleDbConnection OleDbcon = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;");

                OleDbCommand command = new OleDbCommand("SELECT * FROM [Sheet1$]", OleDbcon);
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);

                OleDbcon.Open();
                // Create DbDataReader to Data Worksheet

                OleDbDataReader dr = command.ExecuteReader();

                //SQL Server Connection String
                string constr = @"Data Source=.;Initial Catalog=DTNH;Integrated Security=True";

                // Bulk Copy to SQL Server 
                SqlBulkCopy bulkInsert = new SqlBulkCopy(constr);
                bulkInsert.DestinationTableName = "TbKhoaHoc";
                bulkInsert.WriteToServer(dr);
                OleDbcon.Close();
            }
        }       
    }
}